export const ProjectQueries = {
    GetProjects: `
    SELECT
      *
    FROM my_schema.project
    `,

    GetSubjectProjectChecks: `
    SELECT
     *
    FROM my_schema.project
    WHERE
      projectStatus = '项目未立项'
      AND projectCheckStatus in ('提交项目开题报告', '项目开题报告审批中')
    `,

    GetMidProjectChecks: `
    SELECT
     *
    FROM my_schema.project
    WHERE
      projectStatus = '项目进行中'
      AND projectCheckStatus in ('提交项目中检报告', '项目中期报告审批中')
    `,

    GetFinalProjectChecks: `
    SELECT
     *
    FROM my_schema.project
    WHERE
      projectStatus = '项目进行中'
      AND projectCheckStatus in ('提交项目结项报告', '项目结项报告审批中')
    `,

    GetNotifiedMidProjects: `
    SELECT
     *
    FROM my_schema.project
    WHERE
      NOW() >= projectStartDate+INTERVAL DATEDIFF(projectExpectedEndDate,projectStartDate)/2 DAY 
      AND projectStatus in ('延期','进行中')
    `,

    GetNotifiedFinalProjects: `
    SELECT
     *
    FROM my_schema.project
    WHERE
      NOW() >= (projectExpectedEndDate - INTERVAL 5 DAY) 
      AND projectStatus in ('延期','进行中')
    `,

    GetLastProject: `
    SELECT
      MAX(projectId) as projectMaxId
    FROM my_schema.project
    `,
  
    GetProjectById: `
    SELECT
     *
    FROM my_schema.project
    WHERE
      id = ?
    `,

    GetProjectByProjectId: `
    SELECT
     *
    FROM my_schema.project
    WHERE
      projectId = ?
    `,

    GetProjectsByStatus: `
    SELECT
    *
    FROM my_schema.project
    WHERE
      projectStatus in ?
    `,

    GetProjectsByGroupIds: `
    SELECT
      id,
      projectId,
      projectName,
      projectDeclareName,
      projectGroupId,
      projectGroupName,
      projectDeclareDate,
      projectEndDate,
      projectAttachFileName,
      projectAttachFileUrl,
      projectOwnerId,
      projectOwnerEmployeeId,
      projectOwnerName,
      projectOwnerPhone,
      projectOwnerEmail,
      projectDepartment,
      projectSource,
      projectCategory,
      projectLevel,
      projectDirection,
      projectResearchDirection,
      projectStatus,
      projectCreatedDate,
      projectStartDate,
      projectExpectedEndDate,
      projectApprovedCost,
      projectAppliedCostSource,
      projectAppliedCost,
      projectAppliedCostUse,
      projectAppliedCostUseItems,
      projectAppliedCostItems,
      projectResultForm,
      projectStatisticalYear,
      projectChangeReport,
      projectChangeReportName,
      projectChangeDescription,
      projectEthicalApproverName1,
      projectEthicalApproverName2,
      projectCostApproverName,
      projectPreApproverName,
      projectEndApproverName,
      projectInformationCheckStatus,
      projectInformationCheckHistory,
      projectMidReportName,
      projectMidReport,
      projectMidDate,
      projectMidDescription,
      projectFinalReportName,
      projectFinalReport,
      projectFinalDate,
      projectFinalDescription,
      projectMidApproverName,
      projectFinalApproverName,
      projectCheckStatus,
      projectCheckHistory,
      projectChangeHistory,
      projectApplicantId,
      projectApplicantName,
      projectChangeStatus,
      projectEthicCheckCount
    FROM my_schema.project
    WHERE
      projectGroupId in ?
    `,   

    GetProjectsByOwnerId: `
    SELECT
      id,
      projectId,
      projectName,
      projectDeclareName,
      projectGroupId,
      projectGroupName,
      projectDeclareDate,
      projectEndDate,
      projectAttachFileName,
      projectAttachFileUrl,
      projectOwnerId,
      projectOwnerEmployeeId,
      projectOwnerName,
      projectOwnerPhone,
      projectOwnerEmail,
      projectDepartment,
      projectSource,
      projectCategory,
      projectLevel,
      projectDirection,
      projectResearchDirection,
      projectStatus,
      projectCreatedDate,
      projectStartDate,
      projectExpectedEndDate,
      projectApprovedCost,
      projectAppliedCostSource,
      projectAppliedCost,
      projectAppliedCostUseItems,
      projectAppliedCostUse,
      projectAppliedCostItems,
      projectResultForm,
      projectStatisticalYear,
      projectChangeReport,
      projectChangeReportName,
      projectChangeDescription,
      projectEthicalApproverName1,
      projectEthicalApproverName2,
      projectCostApproverName,
      projectPreApproverName,
      projectEndApproverName,
      projectInformationCheckStatus,
      projectInformationCheckHistory,
      projectMidReportName,
      projectMidReport,
      projectMidDate,
      projectMidDescription,
      projectFinalReportName,
      projectFinalReport,
      projectFinalDate,
      projectFinalDescription,
      projectMidApproverName,
      projectFinalApproverName,
      projectCheckStatus,
      projectCheckHistory,
      projectChangeHistory,
      projectApplicantId,
      projectApplicantName,
      projectChangeStatus,
      projectEthicCheckCount
    FROM my_schema.project
    WHERE
      projectOwnerId = ?
    `,

    GetProjectsByGroupId: `
    SELECT
      id,
      projectId,
      projectName,
      projectDeclareName,
      projectGroupId,
      projectGroupName
    FROM my_schema.project
    WHERE
      projectGroupId = ?
    `,

    GetProjectsByApplicantId: `
    SELECT
      id,
      projectId,
      projectName,
      projectDeclareName,
      projectGroupId,
      projectGroupName,
      projectDeclareDate,
      projectEndDate,
      projectAttachFileName,
      projectAttachFileUrl,
      projectOwnerId,
      projectOwnerEmployeeId,
      projectOwnerName,
      projectOwnerPhone,
      projectOwnerEmail,
      projectDepartment,
      projectSource,
      projectCategory,
      projectLevel,
      projectDirection,
      projectResearchDirection,
      projectStatus,
      projectCreatedDate,
      projectStartDate,
      projectExpectedEndDate,
      projectApprovedCost,
      projectAppliedCostSource,
      projectAppliedCost,
      projectAppliedCostUseItems,
      projectAppliedCostUse,
      projectAppliedCostItems,
      projectResultForm,
      projectStatisticalYear,
      projectChangeReport,
      projectChangeReportName,
      projectChangeDescription,
      projectEthicalApproverName1,
      projectEthicalApproverName2,
      projectCostApproverName,
      projectPreApproverName,
      projectEndApproverName,
      projectInformationCheckStatus,
      projectInformationCheckHistory,
      projectMidReportName,
      projectMidReport,
      projectMidDate,
      projectMidDescription,
      projectFinalReportName,
      projectFinalReport,
      projectFinalDate,
      projectFinalDescription,
      projectMidApproverName,
      projectFinalApproverName,
      projectCheckStatus,
      projectCheckHistory,
      projectChangeHistory,
      projectApplicantId,
      projectApplicantName,
      projectChangeStatus,
      projectEthicCheckCount
    FROM my_schema.project
    WHERE
      projectApplicantId = ?
    `,

    GetProjectsByParticipantId: `
    SELECT
      id,
      projectId,
      projectName,
      projectDeclareId,
      projectDeclareName,
      projectGroupId,
      projectGroupName,
      projectDeclareDate,
      projectEndDate,
      projectOwnerId,
      projectOwnerEmployeeId,
      projectOwnerName,
      projectOwnerPhone,
      projectOwnerEmail,
      projectDepartment,
      projectSource,
      projectCategory,
      projectLevel,
      projectDirection,
      projectResearchDirection,
      projectStatus,
      projectCreatedDate,
      projectStartDate,
      projectExpectedEndDate,
      projectApprovedCost,
      projectAppliedCostSource,
      projectAppliedCost,
      projectAppliedCostUseItems,
      projectAppliedCostUse,
      projectAppliedCostItems,
      projectResultForm,
      projectStatisticalYear,
      projectChangeReport,
      projectChangeReportName,
      projectChangeDescription,
      projectEthicalApproverId1,
      projectEthicalApproverName1,
      projectEthicalApproverId2,
      projectEthicalApproverName2,
      projectFinalApproverId,
      projectFinalApproverName,
      projectCostApproverId,
      projectCostApproverName,
      projectPreApproverId,
      projectPreApproverName,
      projectEndApproverId,
      projectEndApproverName,
      projectApplicantId,
      projectInformationCheckStatus,
      projectApplicantName,
      projectChangeStatus,
      projectEthicCheckCount
    FROM my_schema.project
    WHERE
      projectOwnerId = ?
    OR
      JSON_CONTAINS(projectMembers, ?, '$')
    `,

    AddProject: `
    INSERT INTO my_schema.project (projectId,
      projectName,
      projectDeclareId,
      projectDeclareName,
      projectGroupId,
      projectGroupName,
      projectOwnerId,
      projectOwnerEmployeeId,
      projectOwnerName,
      projectOwnerPhone,
      projectOwnerEmail,
      projectDepartment,
      projectSource,
      projectCategory,
      projectLevel,
      projectDirection,
      projectResearchDirection,
      projectStatus,
      projectDeclareDate,
      projectCreatedDate,
      projectStartDate,
      projectExpectedEndDate,
      projectEndDate,
      projectApprovedCost,
      projectAppliedCostSource,
      projectAppliedCost,
      projectAppliedCostUseItems,
      projectAppliedCostUse,
      projectAppliedCostItems,
      projectResultForm,
      projectStatisticalYear,
      projectAttachFileName,
      projectAttachFileUrl,
      projectChangeReport,
      projectChangeReportName,
      projectChangeDescription,
      projectEthicalApproverId1,
      projectEthicalApproverName1,
      projectEthicalApproverId2,
      projectEthicalApproverName2,
      projectCostApproverId,
      projectCostApproverName,
      projectPreApproverId,
      projectPreApproverName,
      projectEndApproverId,
      projectEndApproverName,
      projectMidReportName,
      projectMidReport,
      projectMidDate,
      projectMidDescription,
      projectFinalReportName,
      projectFinalReport,
      projectFinalDate,
      projectFinalDescription,
      projectMidApproverId,
      projectMidApproverName,
      projectFinalApproverId,
      projectFinalApproverName,
      projectCheckStatus,
      projectCheckHistory,
      projectChangeHistory,
      projectApplicantId,
      projectApplicantName,
      projectInformationCheckStatus,
      projectInformationCheckHistory,
      projectChangeStatus,
      projectEthicCheckCount,
      projectSubjectReport, 
  projectSubjectReportName,
  projectSubjectDate,
  projectSubjectDescription,
  projectSubjectApproverId,
  projectSubjectApproverName)
      VALUES ?
    `,

    UpdateProjectById: `
    UPDATE my_schema.project
    SET ?
    WHERE
      id = ?
    `,

    UpdateProjectStatusById: `
    UPDATE my_schema.project
    SET projectStatus = ?
    WHERE
      id = ?
    `,

    UpdateProjectCostById: `
    UPDATE my_schema.project
    SET projectStatus = ?, projectApprovedCost = ?
    WHERE
      id = ?
    `,
  
    DeleteProjectById: `
    DELETE FROM my_schema.project
    WHERE
      id = ?
    `
  };